#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include "sqliteinterface.h"

#define FILEPATH_MAX (80)

int openDatabase(char *Path)//创建数据库/打开数据库
{
    /*db name start*/
    char *pPath = Path;//数据库已经写好
    char dbName[] = "/smart_home";
    pPath = (char *) malloc (FILEPATH_MAX + 10);
    if (NULL == pPath)
    {
        return DB_ERR;
    }
    memset(pPath, 0, FILEPATH_MAX + 10);
    getcwd(pPath, FILEPATH_MAX);

    memcpy(pPath + strlen(pPath), dbName, strlen(dbName));
	printf("%s\n",pPath);

    if (DB_OK != InitConnection())//初始化数据库
    {
        printf("invoke InitConnection function error!\n");
        return DB_ERR;
    }
    
    //open or create db
    if (DB_OK != OpenConnection(pPath))//如果没有数据库，创建数据库
    {
        printf("invoke OpenConnection function error!\n");
        return DB_ERR;
    }	
    return DB_OK;
}

//select RFID
long selectTable(char *pSelectTable, char *pSelect,char *ret)
{
	char **pTableResult= NULL;
	char pSql[40] = {0};
	int iRow = 0;
	int iColumn = 0;
	int i = 0;
	int j =0;
	sprintf(pSql,"select * from %s where name = '%s'",pSelectTable,pSelect);
	GetTable(pSql, &pTableResult,&iRow, &iColumn);
	
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
	
	if(pTableResult != NULL)
	{
		return 1;
	}
	return 0;
}

//insert weight
int insertTable(char *pSelectTable, char *pName,char *pData)
{
	int  ret;
	ret = TableInsertExecSQL(pSelectTable,pName,pData);//插入
	return ret;
}

//update weight
int updateTable(char *pUpdateTable, char *pName,char *pData)
{
	int ret;
	ret = TableUpdateExecSQL(pUpdateTable,pName,"data",pData);
	return ret;
}






int CreateTable()//创建管理员帐号密码表、用户帐号密码表
{
	char admin[] = "create table admin (id integer primary key autoincrement,"//创建密码
																			" name text not null,"//管理员名字 不能为空
																			" data text not null )";//密码 文本型不能为空
							
	if (DB_ERROR_OK != ExecSQL(admin))//执行ExecSQL语句
    {
		//perror("create admin table error\n");
        return DB_ERR;
    }
 	char weight[] = "create table weight (id integer primary key autoincrement,"//创建weight表
              												"name text not null,"
                           									"data text not null)";
    if (DB_ERROR_OK != ExecSQL(weight))
    {
		//perror("create weight table error\n");
        return DB_ERR;
    }
		
	char RFID[] = "create table card (id integer primary key autoincrement,"//创建RFID表
                                                    	"name text not null,"
                                                    	"data text not null)";
    if (DB_ERROR_OK != ExecSQL(RFID))
    {
		perror("create RFID table error\n");
        return DB_ERR;
    }

	char AdminInsert[]="insert into admin values (1,'admin','admin')";//创建一个管理员账号
	if (DB_ERROR_OK != ExecSQL(AdminInsert))
    {
        printf("insert table sql error\n");
		return DB_ERR;
    }

	char UserInsert[]="insert into user values (1,'user','user')";
	if (DB_ERROR_OK != ExecSQL(UserInsert))
    {
        printf("insert table sql error\n");
		return DB_ERR;
    }
	return DB_OK;
}

int FindCard(char * id)//查找用户注册是否重复
{
	char buf[100]; 
	/*
	char findId[100]="select * from card where card_id = id;
	strcat(findName,buf);
	strcat(findName,"'");
	*/
	char** ppTable = NULL;
	sprintf(buf,"select * from card where card_id = '%s'",id);
    int iRow = 0;
    int iColumn = 0;
	if (DB_ERROR_OK != GetTable(buf, &ppTable, &iRow, &iColumn))
    {
		return DB_ERR;
    }
	if (NULL != ppTable)
    {
		if(iRow >= 1)
		{
			return DB_OK;		
		}
		return DB_ERR;
	}
    ReleaseTableResult(ppTable);
}

int RID(int socketID,char *buffer[])//ID 注册
{
	char buf[50] = "rid,double_err";//注册失败，发送buf
	//printf("user_name = %s\n",buffer[1]);
	if(DB_OK ==FindCard(buffer[1]))//如果注册重复，提示注册名失败
	{
		printf("id zhuce error!\n");
		send(socketID, buf, strlen(buf), 0);//发送给客户端注册失败
		return DB_ERR;
    };
	char idInsert[200]={0};
	sprintf(idInsert,"insert into card (card_id) values ('%s')",buffer[1]);
	if (DB_ERROR_OK != ExecSQL(idInsert))
    {
        printf("id zhuce error!!\n");
		return DB_ERR;
    }
	char buff[30] = "rid,ok";
	send(socketID, buff, strlen(buff), 0);
	return DB_OK;
}



int DID(int socketID,char *buffer[])
{
	char idDel[200]={0};
	sprintf(idDel,"delete from card where card_id = ('%s')",buffer[1]);
	if (DB_ERROR_OK != ExecSQL(idDel))
    {
		char buf[30] = "rid,del_err";
		send(socketID, buf, strlen(buf), 0);
		return DB_ERR;
    }
	char buff[30] = "rid,del_ok";
	send(socketID, buff, strlen(buff), 0);
	return DB_OK;	
}

int Zhuce(int socketID,char *buffer[])//用户帐号注册
{
	char buf[50] = "ZhuceError";//注册失败，发送buf
	//printf("user_name = %s\n",buffer[1]);
	if(DB_OK ==FindTable(buffer[1]))//如果注册重复，提示注册名失败
	{
		printf("username zhuce error!\n");
		send(socketID, buf, strlen(buf), 0);//发送给客户端注册失败
		return DB_ERR;
    };
	char UserInsert[200]="insert into user (user_name, user_password) values ('";
	strcat(UserInsert,buffer[1]);//注册的用户名
	strcat(UserInsert,"','");
	strcat(UserInsert,buffer[2]);//注册的用户密码
	strcat(UserInsert,"')");
	
	if (DB_ERROR_OK != ExecSQL(UserInsert))
    	{
        	printf("username zhuce error!!\n");
		return DB_ERR;
    	}
	//printf("zhuce ok!\n");
	char buff[30] = "ZhuceSucced";
	send(socketID, buff, strlen(buff), 0);
	return DB_OK;
}

int Login(int socketID,char *buffer[])//用户登录
{

	char adminSuccedbuf[50] = "adminLoginSucced";//管理员登录成功
	char userSuccedbuf[50] = "userLoginSucced";//普通用户登录成功
	char Errorbuf[50] = "LoginError";//登录错误
	if(0 ==strcmp("admin",buffer[1]))
	{
		char Admin[100] = "select * from admin where admin_password = '";
		strcat(Admin,buffer[2]);
		strcat(Admin,"'");
		char** ppTable = NULL;
    		int iRow = 0;
    		int iColumn = 0;
		if (DB_ERROR_OK != GetTable(Admin, &ppTable, &iRow, &iColumn))
    		{
			return DB_ERR;
    		}
		if (NULL != ppTable)
    		{
			if(0 == strcmp(buffer[1],ppTable[4]) && 0 == strcmp(buffer[2],ppTable[5]))
			{
				printf("admin login ok!\n");
				send(socketID, adminSuccedbuf, strlen(adminSuccedbuf), 0);
				return DB_OK;		
			}
			printf("admin login error!\n");
			send(socketID, Errorbuf, strlen(Errorbuf), 0);
			return DB_ERR;
		}
	}
	else
	{
		char User[100] = "select * from user where user_name = '";//查找用户名必须是buffer[1]里面的用户名，密码必须是用户密码
		strcat(User,buffer[1]);
		strcat(User,"'");
		strcat(User,"and user_password = '");
		strcat(User,buffer[2]);
		strcat(User,"'");
		char** ppTable = NULL;//查找的内容放在二级指针/select * from user where user_name
    		int iRow = 0;//查找的行
    		int iColumn = 0;//查找的列
		if (DB_ERROR_OK != GetTable(User, &ppTable, &iRow, &iColumn))
    		{
			return DB_ERR;
    		}
		if (NULL != ppTable)
    		{
			if(0 == strcmp(buffer[1],ppTable[4]) && 0 == strcmp(buffer[2],ppTable[5]))
			{
				printf("user login ok!\n");
				send(socketID, userSuccedbuf, strlen(userSuccedbuf), 0);
				return DB_OK;		
			}
			printf("user login error!\n");
			send(socketID, Errorbuf, strlen(Errorbuf), 0);
			return DB_ERR;
		}
	}
}


int FindTable(const char *buf)//查找用户注册是否重复
{
	if(NULL == buf)
	{
		printf("find error\n");
	}
	char findName[100]="select * from user where user_name = '";
	strcat(findName,buf);
	strcat(findName,"'");
	char** ppTable = NULL;
    	int iRow = 0;
    	int iColumn = 0;
	if (DB_ERROR_OK != GetTable(findName, &ppTable, &iRow, &iColumn))
    	{
		return DB_ERR;
    	}
	if (NULL != ppTable)
    	{
		if(0 == strcmp(buf,ppTable[4]))
		{
			return DB_OK;		
		}
		return DB_ERR;
	}
    	ReleaseTableResult(ppTable);
	return DB_OK;
}

int Begin(int socketID,char *buffer[])//发送数据到客户端
{
	char select[100] = "select * from environment";
	char** ppTable = NULL;
    	char* pErrMsg = NULL;
    	int iRow = 0;
    	int iColumn = 0;
	char buf[200] = "begin,";
    	if (DB_ERROR_OK != GetTable(select, &ppTable, &iRow, &iColumn))
    	{
        	printf("select sql error\n");
		return DB_ERR;
    	}
	int a = iRow*5+1;
	int i;
	for(i = 0;i < 4;i++)
	{
		strcat(buf,ppTable[a]);//
		strcat(buf,",");
		a++;
	}
	printf("\n");
	send(socketID, buf, strlen(buf), 0);//将buf传送到客户端
    	ReleaseTableResult(ppTable);
	
	return DB_OK;	
}



